Redshift Federated Queryで異なるアカウントのRDSにアクセスしてみた

どーもsutoです。

皆さん2020年4月にGAとなったAmazon RedshiftのFederated Queryを使ったことはあるでしょうか？

RedshiftのFederated Queryとは、RedshiftからRDSとAuroraのテーブルに直接アクセスできる機能です。

今回はRedshiftからFederated Queryでアクセスした別アカウントのRDS(PosgreSQL）のテーブルをSELECT、UNLOADできるのか検証してみました。

実際にやってみた

検証環境の構築は以下の記事を参考に、同様の手順で設定していますので合わせて読んでいただければと思います。

検証環境の準備

今回の検証では以下のような構成を準備し、アカウントAのReeshiftからアカウントBのRDS（PostgreSQL）のテーブルにSELECT、UNLOAD実行してみます。

• アカウントBに作成するRDSのセキュリティグループは参考記事の①を参考にアカウントAのRedshiftのパブリックIPを許可します。
• アカウントAで作成するRedshiftのIAMロールは参考記事③と同じように作成してアタッチします。
  • 今回はアカウントAに作成したシークレットを取得してRDSにアクセスするため、ロールのポリシーは以下のようになります

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AccessSecret",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Resource": "＜アカウントBのシークレットのArn＞"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetRandomPassword",
                "secretsmanager:ListSecrets"
            ],
            "Resource": "*"
        },
        {
            "Sid": "AllowUseOfkey",
            "Effect": "Allow",
            "Action": [
                "kms:Encrypt",
                "kms:Decrypt",
                "kms:ReEncrypt*",
                "kms:GenerateDataKey*",
                "kms:DescribeKey"
            ],
            "Resource": "＜アカウントBのKMSに作成したCMKのArn＞"
        }
    ]
}

• RDSへ接続する認証情報は参考記事②のように作成し、アカウントBのSecret Managerに保存しています。
  • シークレット作成時、暗号化キーは「CMK」を設定する必要があるので、KMSでカスタマーマネージドキーを作成します
  • 「キーの使用アクセス許可を定義」にアカウントAのRedshiftのIAMロールを許可するようにするため、以下の内容がCMKのキーポリシーに付与されている必要があります。（キーポリシーを編集して設定してください）

        {
            "Sid": "Allow use of the key",
            "Effect": "Allow",
            "Principal": {
                "AWS": "＜RedshiftのIAMロールのArn＞"
            },
            "Action": [
                "kms:Encrypt",
                "kms:Decrypt",
                "kms:ReEncrypt*",
                "kms:GenerateDataKey*",
                "kms:DescribeKey"
            ],
            "Resource": "*"
        }

• また、RedshiftのIAMロールがSecretManagerのシークレットの値を取得できるように、アカウントBのシークレットの「リソースに対するアクセス許可 (オプション)」を編集し、以下のようなポリシーを追加する必要があります。

{
  "Version" : "2012-10-17",
  "Statement" : [ {
    "Effect" : "Allow",
    "Principal" : {
      "AWS" : "＜RedshiftのIAMロールのArn＞"
    },
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "＜アカウントBのシークレットのArn＞"
  } ]
}

外部スキーマ作成とFederated Query実行確認

Redshiftにログインし、RDS PostgreSQLのスキーマに対する外部スキーマを作成します。以下のコマンドを実行します。

• 作成する外部スキーマ名：pg
• 接続するサービス：POSTGRES
• 接続先のデータベース：dev
• 接続先のスキーマ：test
• 接続先のURL：作成したRedshiftのIAMロール
• シークレット（認証情報）のARN：作成したアカウントBのシークレットのArn

cmdb=# CREATE EXTERNAL SCHEMA pg
FROM POSTGRES
DATABASE 'dev'
SCHEMA 'test'
URI '＜アカウントBのRDSのエンドポイント＞'
PORT 5432
IAM_ROLE '＜RedshiftのIAMロールのArn＞'
SECRET_ARN '＜アカウントBのシークレットのArn＞';
CREATE SCHEMA

cmdb=# ALTER SCHEMA pg owner to ad_suto_takeshi;
ALTER SCHEMA

実際にFederated Queryを実行してRDSのテーブルをSELECTで参照できるか確認してみます。

 cmdb=# select * from pg.user;
 id |  name  | address
----+--------+---------
  2 | Suzuki |
  1 | Yamada | Tokyo
  3 | Sato   | Osaka
(3 rows)

アカウントBのRDSに作成しておいたサンプルテーブルが表示できました。

UNLOADコマンドを実行してみる

ついでにUNLOADコマンドを実行してみると

cmdb=# UNLOAD ('SELECT * FROM pg.user')
TO 's3://cm-suto-takeshi/unload/pg/user/user_'
IAM_ROLE 'arn:aws:iam::318507007885:role/cm-redshift-fullaccess-role'
HEADER
DELIMITER ','
ALLOWOVERWRITE
PARALLEL OFF
;
INFO:  UNLOAD completed, 3 record(s) unloaded successfully.
UNLOAD

RedshiftからRDSのテーブルをS3に出力できました。

【参考】その他：別件の作業でハマったこと

別アカウントのRDSでSQLを実行した際に以下のようなエラー発生に遭遇しました。

dev=# SELECT COUNT(*) FROM pg_schema.users;
ERROR:
  -----------------------------------------------
  error:  cannot assign TransactionIds during recovery
  code:      25000
  context:
  query:     0
  location:  pgclient.cpp:735
  process:   padbmaster [pid=28895]
  -----------------------------------------------

※当時の記憶が曖昧ですが、接続先のRDSはリードレプリカであったと思います。

そこで以下のようなSETコマンドを実行してから実行してみると、

dev=# set pg_federation_repeatable_read= off;
SET
dev=# select count(*) from pg_schema.users;
 count
-------
  1562
(1 row)

コマンドを正常に実行することができました！

エラー原因はよくわかっておらず、上記を回避策と言っていいか定かではありませんが、Federated Queryのトランザクションにおいて、分離レベル”Repeatable Read”をOFFにすることで可能となるようです。

まとめ

今回はRedshiftと異なるアカウントにあるRDSの間でFederated Queryを実行する検証でした。

RedshiftのFederated Queryは基本的にデータの参照しかできないようになっていると思っていたので、UNLOADコマンドも問題なく実行できるとわかったことは個人的に大きな収穫でした。

合わせて読みたい

※ちなみにRedshiftのトランザクションの分離レベルは「SERIALIZABLE」がデフォルト設定となっています。